﻿using System;
using System.Collections.Generic;
using System.Web;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;

namespace NsfcSurvey.Web.BzService
{
    public class DataService
    {
        /// <summary>
        /// 增加问题答案
        /// </summary>
        /// <param name="answer"></param>
        public void CreateAnswer(Answer answer)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlString =
                string.Format(
                    "INSERT INTO Answers (DepartmentCode,DepartmentName,Username,Phone,Email,Category,AnswerTime,Answer1,Answer1Input,Answer2,Answer2Input,Answer3,Answer3Input,Answer4,Answer5,Answer6,Answer7,Answer7Input,Answer8,Answer9,Answer10) VALUES ('{0}','{1}','{2}','{3}','{4}',{5},'{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}')",
            answer.DepartmentCode,
            answer.DepartmentName,
            answer.Username,
            answer.Phone,
            answer.Email,
            answer.Category.HasValue ? answer.Category.Value.ToString() : "NULL",
            answer.AnswerTime,
            answer.Answer1, answer.Answer1Input,
            answer.Answer2, answer.Answer2Input,
            answer.Answer3, answer.Answer3Input,
            answer.Answer4,
            answer.Answer5,
            answer.Answer6,
            answer.Answer7,
            answer.Answer7Input,
            answer.Answer8,
            answer.Answer9,
            answer.Answer10
            );


            db.ExecuteNonQuery(CommandType.Text, sqlString);
        }
        /// <summary>
        /// 严格相等
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public Department GetDepartmentByName(string name)
        {
            Database db = DatabaseFactory.CreateDatabase();
            Department department = null;
            IDataReader dataReader = null;
            string sql = string.Format("Select  Id,Code,Name,Category from Departments where name = '{0}'", name);
            dataReader = db.ExecuteReader(CommandType.Text, sql);

            using (dataReader)
            {
                if (dataReader.Read())
                {
                    department = Populate(dataReader);
                }
            }

            return department;
        }

        public List<Department> GetAll()
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<Department> departments = new List<Department>();
            IDataReader dataReader = null;
            string sql = "Select Id,Code,Name,Category from Departments order by id";
            dataReader = db.ExecuteReader(CommandType.Text, sql);

            using (dataReader)
            {
                while (dataReader.Read())
                {
                    departments.Add(Populate(dataReader));
                }
            }

            return departments;
        }

        private Department Populate(IDataReader dataReader)
        {
            Department department = new Department();
            department.Id = (int)dataReader["Id"];
            if (!dataReader.IsDBNull(1))
                department.Code = dataReader.GetString(1);
            department.Name = dataReader.GetString(2);
            //if (!dataReader.IsDBNull(3))
            //    department.ApplicationCount = dataReader.GetInt32(3);
            //if (!dataReader.IsDBNull(4))
            //    department.Rank = dataReader.GetInt32(4);
            //if (!dataReader.IsDBNull(3))
            department.Category = dataReader.GetInt32(3);

            return department;
        }


        public List<Answer> GetAllAnswers()
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<Answer> answers = new List<Answer>();
            IDataReader dataReader = null;
            string sql = "Select DepartmentCode,DepartmentName,AnswerTime from Answers order by AnswerTime desc";
            dataReader = db.ExecuteReader(CommandType.Text, sql);

            using (dataReader)
            {
                while (dataReader.Read())
                {
                    Answer answer = new Answer();
                    answer.DepartmentName = (string)dataReader["DepartmentName"];
                    answer.AnswerTime = (DateTime)dataReader["AnswerTime"];
                    answers.Add(answer);
                }
            }

            return answers;
        }
    }
}